from configparser import ConfigParser
import os
import openpyxl
from openpyxl import load_workbook
import datetime
from openpyxl.styles import Font


def find():
    # 获取目录下的文件名
    doc = os.listdir()
    ex_dir = []
    repeatRow = []
    dealReapt = []
    file = fileName
    if file == '':
        # 获取目录下所有一览表
        docunames = os.listdir()
        for docuname in docunames:
            if os.path.splitext(docuname)[1] == '.xlsx':
                ex_dir.append(docuname)
            if os.path.splitext(docuname)[1] == '.xls':
                print("（不支持xls文件，请转换为xlsx：）" + docuname)
    else:
        ex_dir.append(file)
    # 循环处理同一目录下的所有一览表
    for i in range(0, ex_dir.__len__()):
        # 读取一览表原始数据表
        try:
            modifywb = load_workbook(str(ex_dir[i]))
        except Exception:
            print('目录下未获取到xlsx文件，配置文件内查重文件名需包含完整后缀名')
            input('任意输入退出!\n')
        print("===============>开始查重：" + str(ex_dir[i] +'<==============='))
        # 一览表EXCEL的sheet
        modifySheet = modifywb.worksheets[0]
        print('本文件共有：' + str(modifywb.worksheets[0].max_row) + '行.')
        print('------------------------------------------------------------------------------')
        for cols in range(1, modifySheet.max_column + 1):
            # 根据标题行遍历定位查重列
            if modifySheet.cell(int(titleRow), cols).value == repeat:
                # print(int(titleRow), cols)
                # 按行双循环查重，找到重复的标红
                for rows in range(int(textRow), modifySheet.max_row + 1):
                    # 此循环查出所有重复的行
                    # 找出重复值、且行数不相等，内容不为空
                    for compareRows in range(int(textRow), modifySheet.max_row + 1):
                        if modifySheet.cell(rows, cols).value == modifySheet.cell(compareRows, cols).value\
                                and rows != compareRows and modifySheet.cell(rows, cols).value != None:
                            # 将重复行保存进2个数组，1个避免重复用于记录，另一个用于处理
                            if rows not in repeatRow:
                                repeatRow.append(rows)
                                dealReapt.append(rows)
                            if compareRows not in repeatRow:
                                repeatRow.append(compareRows)
                                dealReapt.append(compareRows)
                    if dealReapt:
                        # 处理单个重复，结束后清空数组
                        print(str(ex_dir[i]) + '行：' + str(dealReapt) + '疑似重复')
                        # 处理重复的行
                        # 临时数组，用于去重保存差异值
                        tempReapt = []
                        # 按列循环，再按重复行循环，比较重复行的列差异，分类操作
                        for reaptsCols in range(1, modifySheet.max_column + 1):
                            for reapts in dealReapt:
                                if reaptsCols == cols:
                                    # 重复行查重列改为红色
                                    modifySheet.cell(reapts, reaptsCols).fill = fill
                                for otherReapts in dealReapt:
                                    # 非同一行
                                    if reapts != otherReapts:
                                        # 内容不同时标红
                                        if modifySheet.cell(reapts, reaptsCols).value != modifySheet.cell(otherReapts, reaptsCols).value:
                                            modifySheet.cell(reapts, reaptsCols).fill = fill
                                            modifySheet.cell(otherReapts, reaptsCols).fill = fill
                                            # 根据配置合并有差异的单元格，先将差异值去重加入数组
                                            if joinMark == '是':
                                                if modifySheet.cell(otherReapts, reaptsCols).value not in tempReapt:
                                                    tempReapt.append(modifySheet.cell(otherReapts, reaptsCols).value)
                                # print(tempReapt)
                            # 数组不为空时，将数组记录的差异值赋值到第一个重复行
                            if tempReapt:
                                tempvalue = ''
                                for j in range(0, tempReapt.__len__()):
                                    if j == 0:
                                        tempvalue = str(tempReapt[j])
                                    else:
                                        tempvalue = tempvalue + str(separator) + str(tempReapt[j])
                                modifySheet.cell(dealReapt[0], reaptsCols).value = str(tempvalue)
                            # 清空数组，用于记录一下个
                            tempReapt = []
                        dealReapt = []
        print('______________________________________________________________________________')
        print('查重完毕：' + str(ex_dir[i]) + '中共有：' + str(repeatRow) + '行疑似存在重复，已标红！\n\n')
        # 另存为
        modifywb.save('（查重' + str(years) + '年' + str(months) + '月' + str(days) + '日' + str(timenow) + ')' + str(ex_dir[i]))
        repeatRow = []


if __name__ == '__main__':
    cf = ConfigParser()
    cf.read('config.ini')
    fileName = cf.get('配置项', '查重文件名')
    titleRow = cf.get('配置项', '标题所在行')
    textRow = cf.get('配置项', '内容开始行')
    repeat = cf.get('配置项', '查重列')
    merge = cf.get('配置项', '合并列')
    joinMark = cf.get('配置项', '是否合并有差异的单元格')
    separator = cf.get('配置项', '合并分隔符')
    descending = cf.get('配置项', '降序排序列')
    ascending = cf.get('配置项', '升序排序列')
    # 获取当前年月日
    years = datetime.datetime.now().year
    months = datetime.datetime.now().month
    if months < 10:
        months = '0' + str(months)
    days = datetime.datetime.now().day
    if days < 10:
        days = '0' + str(days)
    timenow = datetime.datetime.now().strftime('%H：%M')
    # 标红单元格
    fill = openpyxl.styles.PatternFill("solid", fgColor="FFFF0000")
    # print(fileName, titleRow, textRow, repeat, merge, separator, descending, ascending)
    find()
    print('\n\n—————————————————————————————————————————————————————\n'
          '————打包环境：Windows Server 2016 Standard—————————————————\n'
          '————        python 3.10                 ——————————————————\n'
          '————        ver1.0                      ——————————————————\n'
          '——————————————————————by Wenwg          —————————\n'
          '——————————————————————185185330@qq.com  —————————\n'
          '——————————————————————2022/05/20        —————————\n')
    input('任意输入退出！\n')
